﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ChineseSchool.Models.Hethong;
using System.Data;

namespace ChineseSchool.BLL
{
    public class B_User
    {
        private PetaPoco.Database db;
        public B_User()
        {
            db = new PetaPoco.Database("ConnectionString");
        }

        public List<M_User> GetAlls()
        {
            var User = db.Fetch<M_User>(@";EXEC User_Get_List");
            return User;
        }

        public List<M_User> SelectTopUsers()
        {
            var User = db.Fetch<M_User>(@";EXEC User_Get_Top_Users");
            return User;
        }

        // Select by ID
        public M_User Selectby_id(int UserID)
        {
            var loadUserType = db.SingleOrDefault<M_User>("SELECT * FROM tiengtrungminha.Users bc WHERE bc.UserID = " + UserID + ";");
            db.CloseSharedConnection();
            return loadUserType;
        }

        public M_User Selectby_username_password(string username, string password)
        {
            var loadUserType = db.SingleOrDefault<M_User>("SELECT * FROM tiengtrungminha.Users bc WHERE bc.UserName = '" + username + "' AND bc.password = '" + password + "';");
            db.CloseSharedConnection();
            return loadUserType;
        }

        public List<M_User> GetBy_UserTypeID(int UserTypeID)
        {
            var User = db.Fetch<M_User>(@";EXEC User_GetByUserTypeId @@UserTypeId = @0", UserTypeID);
            return User;
        }

        public int CheckUserExist(string UserName)
        {
            int count = db.ExecuteScalar<int>("SELECT Count(*) FROM tiengtrungminha.Users where UserName='" + UserName + "'");
            return count;
        }

        public int CheckLogin(string UserName, string Password)
        {
            int count = db.ExecuteScalar<int>("SELECT Count(*) FROM tiengtrungminha.Users where UserName='" + UserName + "' AND Password = '" + Password + "'");
            return count;
        }

        #region Quản lý group member

        public int CheckUserGroup(string username, int type)
        {
            int count = db.ExecuteScalar<int>(@"SELECT TOP 1 COUNT(*) FROM tiengtrungminha.GroupMember WHERE GroupId = " + type + "AND UserName = '" + username + "'");
            return count;
        }

        public List<M_User> GetUserInGroup(int type)
        {
            var sqlStr = @"SELECT [tiengtrungminha].[Users].[UserID], [tiengtrungminha].[Users].[UserName], [tiengtrungminha].[Users].[JoinDate]
                            FROM [tiengtrungminha].[Users]
                            INNER JOIN [tiengtrungminha].[GroupMember]
                            ON [tiengtrungminha].[Users].[UserName] = [tiengtrungminha].[GroupMember].[UserName]
                            WHERE [tiengtrungminha].[GroupMember].[GroupId] = " + type;

            var lstUser = db.Fetch<M_User>(sqlStr);
            return lstUser;
        }

        public bool DeleteGroupMember(string username, int type)
        {
            var result = db.Execute(@"DELETE FROM GroupMember
                        WHERE GroupId = " + type + " AND UserName = '" + username + "'");
            if (result > 0)
                return true;
            return false;
        }

        public bool AddGroupMember(string username, int type)
        {
            var result = db.Execute(@"INSERT INTO GroupMember(GroupId, UserName) VALUES(" + type + "," + username + ")");
            if (result > 0)
                return true;
            return false;
        }

        #endregion

        public int CheckEmailExist(string Email)
        {
            int count = db.ExecuteScalar<int>(@";EXEC User_GetByEmail @@Email = @0", Email);
            return count;
        }

        public List<M_User> GetTopUser()
        {
            var strSql = @"SELECT TOP 5 UserID, UserName, Avatar, JoinDate
                            FROM Users
                            WHERE IsTop = '1'
                            ORDER BY JoinDate DESC";
            var lstTopUser = db.Fetch<M_User>(strSql);
            return lstTopUser;
        }
    }
}